package com.example.service;

import com.example.shiti.ProductItemInfo;
import com.example.shiti.TradeInfo;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

@Repository
public class TradeInfoRepository {

    // 从配置文件中获取数据库连接参数
    @Value("${spring.datasource.url}")
    private String url = "jdbc:mysql://localhost:3306/azf-erp?useSSL=false&serverTimezone=UTC";

    @Value("${spring.datasource.username}")
    private String user = "root";

    @Value("${spring.datasource.password}")
    private String password = "";

    public void saveTradeInfo(TradeInfo tradeInfo) {
        if (tradeInfo == null) {
            throw new IllegalArgumentException("TradeInfo cannot be null");
        }

        // SQL 语句，确保参数数量与列数量一致
        String insertTradeInfoSQL = "INSERT IGNORE INTO trade_info (id, currency, modify_time, create_time, pay_time, receiving_time, all_delivered_time, complete_time, buyer_contact_name, buyer_contact_mobile, buyer_contact_phone, buyer_contact_province, buyer_contact_city, buyer_contact_area, buyer_contact_town, buyer_contact_address, buyer_contact_zip, caid, oaid, status, refund_status, remark, ascp_supplier_id, ascp_supplier_name, shipping_fee, total_amount) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        String insertProductItemInfoSQL = "INSERT IGNORE INTO product_item_info (product_item_extend_fields_key, product_item_extend_fields_value, cargo_number, item_amount, name, product_cargo_number, productid, product_img_url, quantity, refund_id, refund_status, skuid, sku_infos_name, sku_infos_value, status, sub_itemid, trade_info_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        Connection connection = null;

        try {
            connection = DriverManager.getConnection(url, user, password);
            // 开始事务
            connection.setAutoCommit(false);

            // 插入交易信息
            try (PreparedStatement tradeInfoStmt = connection.prepareStatement(insertTradeInfoSQL)) {
                tradeInfoStmt.setString(1, safeGetId(tradeInfo.getId()));
                tradeInfoStmt.setString(2, safeGetString(tradeInfo.getCurrency()));
                tradeInfoStmt.setTimestamp(3, safeGetTimestamp(tradeInfo.getModifyTime()));
                tradeInfoStmt.setTimestamp(4, safeGetTimestamp(tradeInfo.getCreateTime()));
                tradeInfoStmt.setTimestamp(5, safeGetTimestamp(tradeInfo.getPayTime()));
                tradeInfoStmt.setTimestamp(6, safeGetTimestamp(tradeInfo.getReceivingTime()));
                tradeInfoStmt.setTimestamp(7, safeGetTimestamp(tradeInfo.getAllDeliveredTime()));
                tradeInfoStmt.setTimestamp(8, safeGetTimestamp(tradeInfo.getCompleteTime()));
                tradeInfoStmt.setString(9, safeGetString(tradeInfo.getBuyerContactName()));
                tradeInfoStmt.setString(10, safeGetString(tradeInfo.getBuyerContactMobile()));
                tradeInfoStmt.setString(11, safeGetString(tradeInfo.getBuyerContactPhone()));
                tradeInfoStmt.setString(12, safeGetString(tradeInfo.getBuyerContactProvince()));
                tradeInfoStmt.setString(13, safeGetString(tradeInfo.getBuyerContactCity()));
                tradeInfoStmt.setString(14, safeGetString(tradeInfo.getBuyerContactArea()));
                tradeInfoStmt.setString(15, safeGetString(tradeInfo.getBuyerContactTown()));
                tradeInfoStmt.setString(16, safeGetString(tradeInfo.getBuyerContactAddress()));
                tradeInfoStmt.setString(17, safeGetString(tradeInfo.getBuyerContactZip()));
                tradeInfoStmt.setString(18, safeGetString(tradeInfo.getCaid()));
                tradeInfoStmt.setString(19, safeGetString(tradeInfo.getOaid()));
                tradeInfoStmt.setString(20, safeGetString(tradeInfo.getStatus()));
                tradeInfoStmt.setString(21, safeGetString(tradeInfo.getRefundStatus()));
                tradeInfoStmt.setString(22, safeGetString(tradeInfo.getRemark()));
                tradeInfoStmt.setString(23, safeGetString(tradeInfo.getAscpSupplierId()));
                tradeInfoStmt.setString(24, safeGetString(tradeInfo.getAscpSupplierName()));
                tradeInfoStmt.setBigDecimal(25, tradeInfo.getShippingFee()); // 可以为 null
                tradeInfoStmt.setBigDecimal(26, tradeInfo.getTotalAmount()); // 可以为 null

                tradeInfoStmt.executeUpdate();
            }

            // 获取刚插入的交易 ID
            String tradeInfoId = safeGetId(tradeInfo.getId()); // 假设 ID 是手动设置的
            for (ProductItemInfo item : tradeInfo.getProductItems()) {
                if (item == null) {
                    continue; // 跳过 null 的条目
                }
                try (PreparedStatement productItemStmt = connection.prepareStatement(insertProductItemInfoSQL)) {
                    productItemStmt.setString(1, safeGetString(item.getProductItemExtendFieldsKey())); // product_item_extend_fields_key
                    productItemStmt.setString(2, safeGetString(item.getProductItemExtendFieldsValue())); // product_item_extend_fields_value
                    productItemStmt.setString(3, safeGetString(item.getCargoNumber())); // cargo_number
                    productItemStmt.setBigDecimal(4, item.getItemAmount()); // item_amount
                    productItemStmt.setString(5, safeGetString(item.getName())); // name
                    productItemStmt.setString(6, safeGetString(item.getProductCargoNumber())); // product_cargo_number
                    productItemStmt.setLong(7, item.getProductID()); // productid
                    productItemStmt.setString(8, safeGetString(item.getProductImgUrl())); // product_img_url
                    productItemStmt.setBigDecimal(9, item.getQuantity()); // quantity
                    productItemStmt.setString(10, safeGetString(item.getRefundId())); // refund_id
                    productItemStmt.setString(11, safeGetString(item.getRefundStatus())); // refund_status
                    productItemStmt.setLong(12, item.getSkuID()); // skuid
                    productItemStmt.setString(13, safeGetString(item.getSkuInfosName())); // sku_infos_name
                    productItemStmt.setString(14, safeGetString(item.getSkuInfosValue())); // sku_infos_value
                    productItemStmt.setString(15, safeGetString(item.getStatus())); // status
                    productItemStmt.setString(16, safeGetString(item.getSubItemID())); // sub_itemid
                    productItemStmt.setString(17, safeGetString(item.getTradeInfoId())); // trade_info_id
                    productItemStmt.executeUpdate();

                }
            }
            // 提交事务
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            // 回滚事务
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException rollbackException) {
                    rollbackException.printStackTrace();
                }
            }
        } finally {
            // 关闭连接
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException closeException) {
                    closeException.printStackTrace();
                }
            }
        }
    }

    // 辅助方法，用于防止空指针异常
    private String safeGetId(String id) {
        return id != null ? id : ""; // 返回空字符串或其他默认值
    }

    private String safeGetString(String value) {
        return value != null ? value : ""; // 返回空字符串或其他默认值
    }

    private java.sql.Timestamp safeGetTimestamp(java.util.Date date) {
        return date != null ? new java.sql.Timestamp(date.getTime()) : null; // 如果日期为 null，返回 null
    }
}
